package mypetstore.persistence.impl;

import mypetstore.domain.Item;
import mypetstore.domain.Product;
import mypetstore.persistence.DBUtil;
import mypetstore.persistence.ItemDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ItemDaoImpl implements ItemDao {

    // 修复的 SQL 语句 - 移除错误的别名
    private static final String GET_ITEM_LIST_BY_PRODUCT =
            "SELECT I.ITEMID, I.LISTPRICE, I.UNITCOST, I.SUPPLIER, " +
                    "I.PRODUCTID, P.NAME, P.DESCN, P.CATEGORY, " +
                    "I.STATUS, I.ATTR1, I.ATTR2, I.ATTR3, I.ATTR4, I.ATTR5 " +
                    "FROM ITEM I, PRODUCT P " +
                    "WHERE I.PRODUCTID = P.PRODUCTID AND I.PRODUCTID = ?";

    private static final String GET_ITEM =
            "SELECT I.ITEMID, I.LISTPRICE, I.UNITCOST, I.SUPPLIER, " +
                    "I.PRODUCTID, P.NAME, P.DESCN, P.CATEGORY, " +
                    "I.STATUS, I.ATTR1, I.ATTR2, I.ATTR3, I.ATTR4, I.ATTR5, V.QTY " +
                    "FROM ITEM I, INVENTORY V, PRODUCT P " +
                    "WHERE P.PRODUCTID = I.PRODUCTID AND I.ITEMID = V.ITEMID AND I.ITEMID = ?";

    private static final String GET_INVENTORY_QUANTITY = "SELECT QTY AS value FROM INVENTORY WHERE ITEMID = ?";
    private static final String UPDATE_INVENTORY_QUANTITY = "UPDATE INVENTORY SET QTY = QTY - ? WHERE ITEMID = ?";

    @Override
    public void updateInventoryQuantity(Map<String, Object> param) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement statement = connection.prepareStatement(UPDATE_INVENTORY_QUANTITY);
            String itemId = param.keySet().iterator().next();
            Integer increment = (Integer) param.get(itemId);
            statement.setInt(1, increment.intValue());
            statement.setString(2, itemId);
            statement.executeUpdate();
            DBUtil.closePreparedStatement(statement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public int getInventoryQuantity(String itemId) {
        int result = -1;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement statement = connection.prepareStatement(GET_INVENTORY_QUANTITY);
            statement.setString(1, itemId);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                result = resultSet.getInt(1);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(statement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public List<Item> getItemListByProduct(String productId) {
        List<Item> itemList = new ArrayList<Item>();
        System.out.println("=== ItemDaoImpl: 开始查询商品列表，productId: " + productId + " ===");

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement statement = connection.prepareStatement(GET_ITEM_LIST_BY_PRODUCT);
            statement.setString(1, productId);
            ResultSet resultSet = statement.executeQuery();

            int count = 0;
            while (resultSet.next()) {
                count++;
                Item item = new Item();
                // 使用列名而不是索引
                item.setItemId(resultSet.getString("ITEMID"));
                item.setListPrice(resultSet.getBigDecimal("LISTPRICE"));
                item.setUnitCost(resultSet.getBigDecimal("UNITCOST"));
                item.setSupplierId(resultSet.getInt("SUPPLIER"));
                item.setProductId(resultSet.getString("PRODUCTID"));

                // 创建并设置 Product 对象
                Product product = new Product();
                product.setProductId(resultSet.getString("PRODUCTID"));
                product.setName(resultSet.getString("NAME"));
                product.setDescription(resultSet.getString("DESCN"));
                product.setCategoryId(resultSet.getString("CATEGORY"));
                item.setProduct(product);

                item.setStatus(resultSet.getString("STATUS"));
                item.setAttribute1(resultSet.getString("ATTR1"));
                item.setAttribute2(resultSet.getString("ATTR2"));
                item.setAttribute3(resultSet.getString("ATTR3"));
                item.setAttribute4(resultSet.getString("ATTR4"));
                item.setAttribute5(resultSet.getString("ATTR5"));

                itemList.add(item);
                System.out.println("=== 读取商品 " + count + ": " + item.getItemId() +
                        ", 属性: " + item.getAttribute1() + " ===");
            }

            System.out.println("=== ItemDaoImpl: 查询完成，共找到 " + count + " 个商品 ===");

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(statement);
            DBUtil.closeConnection(connection);

        } catch (Exception e) {
            System.out.println("=== ItemDaoImpl: 查询商品列表时出现异常 ===");
            e.printStackTrace();
        }
        return itemList;
    }

    @Override
    public Item getItem(String itemId) {
        Item item = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement statement = connection.prepareStatement(GET_ITEM);
            statement.setString(1, itemId);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                item = new Item();
                item.setItemId(resultSet.getString("ITEMID"));
                item.setListPrice(resultSet.getBigDecimal("LISTPRICE"));
                item.setUnitCost(resultSet.getBigDecimal("UNITCOST"));
                item.setSupplierId(resultSet.getInt("SUPPLIER"));
                item.setProductId(resultSet.getString("PRODUCTID"));

                Product product = new Product();
                product.setProductId(resultSet.getString("PRODUCTID"));
                product.setName(resultSet.getString("NAME"));
                product.setDescription(resultSet.getString("DESCN"));
                product.setCategoryId(resultSet.getString("CATEGORY"));
                item.setProduct(product);

                item.setStatus(resultSet.getString("STATUS"));
                item.setAttribute1(resultSet.getString("ATTR1"));
                item.setAttribute2(resultSet.getString("ATTR2"));
                item.setAttribute3(resultSet.getString("ATTR3"));
                item.setAttribute4(resultSet.getString("ATTR4"));
                item.setAttribute5(resultSet.getString("ATTR5"));
                item.setQuantity(resultSet.getInt("QTY"));

                DBUtil.closeResultSet(resultSet);
                DBUtil.closePreparedStatement(statement);
                DBUtil.closeConnection(connection);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return item;
    }
}